package org.bigdata.framework.core.utils;

public class ConstantSql {
	
	/**
	 * 获取所有数据库
	 */
	public static String mysql_databases = "SHOW DATABASES";//SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`;
	/**
	 * 获取库中所有表名称(需要+'数据库名称')
	 */
	public static String mysql_tables = "SELECT TABLE_NAME NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ";
	/**
	 * 获取表的列名(需要+'表名')
	 */
	public static String mysql_column = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = ";
	/**
	 * 获取表的类型(需要+'表名')
	 */
	public static String mysql_type = "SELECT DATA_TYPE FROM information_schema.columns WHERE table_name = ";
	/**
	 * 获取表的列名称——类型(需要+'表名')
	 */
	public static String mysql_column_type = "SELECT COLUMN_NAME,DATA_TYPE FROM information_schema.columns WHERE table_name = ";
	
	/**
	 * 获取库中所有表名称
	 */
	public static String oracle_tables = "SELECT TABLE_NAME NAME FROM user_tables ORDER BY TABLE_NAME";
	
	/**
	 * 获取表的列 名称--类型--字段长度(需要+upper('表名'))
	 */
	public static String oracle_column = "SELECT column_name,data_type,data_length FROM user_tab_columns WHERE table_name=";
	
	/**
	 * 查看所有用户
	 */
	public static String oracle_users="SELECT USERNAME FROM dba_users ORDER BY USERNAME";
	
	/**
	 * 查看所有数据库
	 */
	public static String sqlserver_database="SELECT name FROM sysdatabase";
	/**
	 * 查看所有表 （需要+库名称）
	 */
	public static String sqlserver_tables="SELECT NAME FROM ";
	
	/**
	 * 查看字段名称 ~~~~注意：Object_Id(表名称)
	 */
	public static String sqlserver_column="SELECT NAME FROM SysColumns WHERE id=Object_Id";
	
	/**
	 * 查询数据库所有表---MSysObjects权限设置只读
	 * 设置方法：
	 * 	2007：
	 * 		1.文件->选项->当前库->导航->导航选项->显示隐藏对象、显示系统对象前面打勾
	 * 		2.文件->信息->管理用户和权限->全向->对象名称->选中MSysObjects->读取数据库前面打勾
	 * 	2003：
	 * 		1.工具 -> 选项 -> 视图 -> 隐藏对象、系统对象前面的勾选上。
	 * 		2.工具 -> 安全 -> 用户与组的权限在对象名称中选定 MSysObjects 
	 */
	public static String access_tables="select NAME from MSysObjects where Type=1 and flags=0";
	
	
	/*分页查询语句*/
	
	//MySQL= select * from tableName order by id asc LIMIT 0,10000;
	//oracle =SELECT * FROM (  SELECT A.*, ROWNUM RN  FROM (SELECT * FROM T1) A WHERE ROWNUM <= 40) WHERE RN >= 21;
	//sqlserver=SELECT w2.n, w1.* FROM ARTICLE w1, (SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC   
	//access=
}
